
/****** Object:  StoredProcedure [dbo].[VDBA_FailedJobs]    Script Date: 03/25/2013 13:26:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[VDBA_FailedJobs]
AS

BEGIN
SET NOCOUNT ON

DECLARE @CompanyNm varchar(100),@dt varchar(30), @mailAddress varchar(255),@Mail_Id varchar(255)
DECLARE @subj varchar(100),@cmd varchar(2000)
DECLARE @SMTPServer varchar(100),@Domain varchar(64),@senderMailId varchar(64),@mail_str varchar(3000)
DECLARE @path varchar(255), @str varchar(2000)
DECLARE @categories varchar(1000), @count int
DECLARE @jobname VARCHAR(1000), @sendjobnames varchar(1000), @jobnum int, @mailjobnames varchar(75)


SET @mailAddress = <VDBA DB,Varchar(130),Master>.dbo.GetMail()
SET @Mail_Id = <VDBA DB,Varchar(130),Master>.dbo.GetMail()
SET @CompanyNm = <VDBA DB,Varchar(130),Master>.dbo.GetCompanyNm()
SET @path = <VDBA DB,Varchar(130),Master>.dbo.GetVDBAHOME()
SET @SMTPServer = <VDBA DB,Varchar(130),Master>.dbo.GetSMTP()
SET @Domain = <VDBA DB,Varchar(130),Master>.dbo.GetDomain()
SET @senderMailId = <VDBA DB,Varchar(130),Master>.dbo.GetSenderMailId()
--Change the categories that you want to watch for in the next line
SET @categories = '''VDBA'',''Database Maintenance'''
SET @subj = 'VDBA Sql Server Jobs report ' + @CompanyNm
SET @dt = CONVERT(varchar(30),GETDATE(),121)
SET @dt = LEFT(@dt,LEN(@dt) - 4)
SET @dt = RTRIM(LTRIM(@dt))
SET @jobnum=1


--------------------------- WRITE THE HEADER LINE
select @cmd= 'set nocount on select ''HEADER INFO'' +  ''|'' +''' + RTRIM(LTRIM(@dt)) +'''+ ''|'' +'''+ RTRIM(LTRIM(@CompanyNm)) + ''' + ''|''  + '  + '@@servername'  + ' + ''|'' + ' + '@@servicename' + ' + ''|'' + ''' + RTRIM(LTRIM(@Mail_Id)) + '''+ ''|''' 
--select @str = 'sqlcmd -Q" '+ @cmd+ ' "  -w 200 -h-1 -S"" -E -o' + @path + '\tmp\VDBA_JobsReport1.fil'
select @str = 'sqlcmd -Q"'+ @cmd + '" -S' + @@serverName + ' -W -h-1 -s""  -E -o' + @path + '\tmp\VDBA_JobsReport1.fil'
exec master..xp_cmdshell @str, NO_OUTPUT

--------------------------- WRITE THE VERSION LINE
select @cmd ='set nocount on select ''VERSION|''  '+ '+'  + 'SUBSTRING(@@version,CHARINDEX('' on'',@@version) + 4,CHARINDEX('' (Build'',@@version) - (CHARINDEX('' on'',@@version) + 4))'  
select @cmd = @cmd  + '+''|''+' + 'REPLACE(LEFT(@@version,CHARINDEX(''-'',@@version) - 1),''  '','' '') '  + '+''|''+' + 'CAST(SERVERPROPERTY(''productlevel'') as varchar(20))' + '+''|''+' + 'CONVERT(varchar(5),GETDATE(),114) '+ '+''|'''
--select @str = 'sqlcmd -Q" '+ @cmd+ ' "  -w 200 -h-1 -S"" -E  -o' + @path + '\tmp\VDBA_JobsReport2.fil'
select @str = 'sqlcmd -Q"'+ @cmd + '" -S' + @@serverName + ' -W -h-1 -s""  -E -o' + @path + '\tmp\VDBA_JobsReport2.fil'
exec master..xp_cmdshell @str, NO_OUTPUT

----------------------------Get the jobnames that failed in the past 24 hours and save to $VDBA\tmp\VDBA_FailedJobsList.txt

SET NOCOUNT ON
SET NOCOUNT ON
IF OBJECT_ID('tempdb..##jobhistory') IS NOT NULL
BEGIN
   DROP TABLE ##jobhistory
END

IF OBJECT_ID('tempdb..##jobnames') IS NOT NULL
BEGIN
   DROP TABLE ##jobnames
END
SELECT Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,
j.name AS job_name, j.job_id as job_id, j.category_id as category_id,
CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS job_status,
CASE jh.run_status WHEN 0 THEN 'Error Failed'
                WHEN 1 THEN 'Succeeded'
                WHEN 2 THEN 'Retry'
                WHEN 3 THEN 'Cancelled'
                WHEN 4 THEN 'In Progress' ELSE
                'Status Unknown' END AS 'last_run_status',
ja.run_requested_date as last_run_date,
CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,ja.next_scheduled_run_date,
CONVERT(VARCHAR(500),jh.message) AS step_description
into ##jobhistory
FROM
(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)
join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
WHERE jh.run_status not in (1,4) and ja.session_id=(SELECT MAX(session_id)  from msdb.dbo.sysjobactivity) ORDER BY job_name,job_status

Select @cmd= 'SELECT C.name Category, J.* FROM ##jobhistory J INNER JOIN msdb..syscategories C ON '
+'J. category_id = C.category_id where C.name in ('+@categories+') and J.last_run_date > CONVERT(char(30), DATEADD(DAY, -1,GETDATE()),121)'



--select @str = 'sqlcmd -Q" '+ @cmd+ ' "  -w 200 -h-1 -S"" -E -o' + @path + '\tmp\VDBA_FailedJobsList.fil'
select @str = 'sqlcmd -Q"'+ @cmd + '" -S' + @@serverName + ' -W -h-1 -s""  -E -o' + @path + '\tmp\VDBA_FailedJobsList.fil'
exec master..xp_cmdshell @str, NO_OUTPUT

--------------------------------Get all jobs names to send in the alert
Select @cmd= 'SELECT J.job_name into ##jobnames FROM ##jobhistory J INNER JOIN msdb..syscategories C ON '
+'J. category_id = C.category_id where C.name in ('+@categories+') and J.last_run_date > CONVERT(char(30), DATEADD(DAY, -1,GETDATE()),121)'

EXEC (@cmd)

DECLARE JobCursor CURSOR FOR 
SELECT job_name from ##jobnames

SET @sendjobnames=''
SELECT @count=COUNT(*) from ##jobnames

OPEN JobCursor  
FETCH NEXT FROM JobCursor INTO @jobname

WHILE @@FETCH_STATUS = 0 
BEGIN 
	 IF ((@jobnum < @count) and (@count !=1))
	 BEGIN
		SELECT @sendjobnames = @sendjobnames + @jobname+', '
		SET @jobnum=@jobnum+1
	 END
	 ELSE
	 BEGIN
		SELECT @sendjobnames = @sendjobnames + @jobname
	 END
FETCH NEXT FROM JobCursor INTO @jobname
END 
CLOSE JobCursor  
DEALLOCATE JobCursor

SELECT @mailjobnames =  LEFT (@sendjobnames, 75)

-----------------------------------------Remove unwanted characters
BEGIN
    While PatIndex('%[^A-Za-z0-9 ,]%', @mailjobnames) > 0
        Set @mailjobnames = Stuff(@mailjobnames, PatIndex('%[^a-zA-Z0-9 ,]%', @mailjobnames), 1, ' ')

   
END

------------------------------------ WRITE the Data output lines --
if (@count > 0)
BEGIN
select @cmd = 'set nocount on  select ''client_alerts|Check the jobs '+@mailjobnames+'|309|'' +'+ ''''+ @dt + '|''' + '+''' + @Mail_Id + '|'''
--select @str = 'sqlcmd -Q" '+ @cmd+ ' "  -w 200 -h-1 -S"" -E -o ' + @path + '\tmp\VDBA_JobsReport3.fil'
select @str = 'sqlcmd -Q"'+ @cmd + '" -S' + @@serverName + ' -W -h-1 -s""  -E -o' + @path + '\tmp\VDBA_JobsReport3.fil'
exec master..xp_cmdshell @str, NO_OUTPUT


select @str = 'type '+@path +'\tmp\VDBA_JobsReport1.fil '+@path+'\tmp\VDBA_JobsReport2.fil '+@path+'\tmp\VDBA_JobsReport3.fil '+' > '+@path + '\tmp\VDBA_JobsReport.txt'
exec master..xp_cmdshell @str, NO_OUTPUT


------------------------------------If there are any failed jobs then send the alert.

set @mail_str = @path + '\bin\mailsend -d '+@Domain+' -smtp '+@SMTPServer+' -t '+@mailAddress+ ' -f  '+@senderMailId +' -sub Vdba_JobsReport -a '+@path+'\tmp\VDBA_JobsReport.txt -y application/octet-stream'

------If using VDBA_DeliverFile then comment the next line 
------and uncomment the line for Exec VDBA_DeliverFile 
--exec master..xp_cmdshell @mail_str, NO_OUTPUT

Exec VDBA_DeliverFile 'VDBA_JobsReport.txt'

END

END

GO

/****** Object:  StoredProcedure [dbo].[VDBA_PerfStats]    Script Date: 03/25/2013 13:26:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO